import com.teradata.trm.pe.common.task.ProcessingEngineTask;
import com.teradata.trm.common.workflow.TaskException;
import com.teradata.trm.common.context.ContextUtil;
import com.teradata.trm.common.handle.HandleUtil;
import com.teradata.trm.segmentation.domain.Segment;
import com.teradata.trm.segmentation.service.SegmentationQBService;
import com.teradata.trm.pe.common.RunDetails;
import com.teradata.trm.common.db.Result;
import com.teradata.trm.common.util.Strings;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.io.*;

import java.util.List;
import java.util.LinkedList;

public class ResponseLoader extends ProcessingEngineTask {

    private String runId;
    private String viewPrefix;
    private boolean dropViews = false;
    private String segmentListScript;
    private String sqlStatementScript;
    private List<String> segmentList = new LinkedList<String>();
    private String sqlStatement;

    @Override
    public Serializable execute() throws TaskException {
        try {
            info("Running SQL view generation for segmments ");
            info("Developed by EMEA CRM COE");
            info("October 2010");
            info("------------------------------------------");
            RunDetails details = getRunDetails();
            runId = details.getRunId();
            sqlStatement = loadScript(sqlStatementScript);
            info("Running SQL to fetch segment list...");
            processSegmentList(loadScript(segmentListScript));
            info("Total of " + segmentList.size() + " segments found");
            String currentSegment;
            for(int i = 0; i < segmentList.size() ; i++) {
                currentSegment = segmentList.get(i);
                info("Processing segment " + currentSegment);
                if(generateSegmentView(currentSegment)) {
                    executeSQLStatement(currentSegment);
                    dropView(currentSegment);
                }
            }
            return 0;
        }
        catch (Exception ex) {
            info(ex.getMessage());
            throw new TaskException(ex);
        }
        finally {
            shutdown();
        }
    }
    private void executeSQLStatement(String segmentId) {
        String sql = new String(sqlStatement);
        sql = Strings.replace(sql, "@runId", runId);
        sql = Strings.replace(sql, "@viewPrefix", viewPrefix);
        sql = Strings.replace(sql, "@segId", segmentId);
        info("Executing SQL:");
        executeSql(sql);
    }
    private void processSegmentList(String sql) {
        Result result = runQuery(sql);
        if (result.hasErrors())
            throw new TaskException("Failed to get segment details: " + result.getErrors().get(0));
        ResultSet set = result.getResultSet();
        try {
            while (set.next()) {
                segmentList.add(set.getString(1));
            }
        } catch (SQLException sex) {
            throw new TaskException(sex.getMessage());
        }
        if (set != null)
            result.discard();
    }
    
    private boolean generateSegmentView(String segmentId) throws TaskException {
        info("Getting SQL for segment " + segmentId);
        Segment segment = (Segment) HandleUtil.getHandle(segmentId, (short) 2).ref();
        //info("got the handle");
        SegmentationQBService segQB = ContextUtil.getBean("segQueryBuilderService");
        //info("got the bean");
        String sql = "";
        try {
            sql = segQB.getFinalSql(segment, true);
            //info("SQL: ");
            //info(sql);
            sql = "REPLACE VIEW @SpindleTemp." + viewPrefix + "_" + runId + "_" + segmentId + " AS " + sql;
            info("Generating VIEW:");
            executeSql(sql);
            return true;
        } catch (Exception ex) {
            info("Could not get SQL for segment " + segmentId);
            info(ex.getMessage());
            return false;
        }
       
    }

    private void dropView(String segmentId) {
        if (dropViews) {
            executeSql("DROP VIEW @SpindleTemp." + viewPrefix + "_" + runId + "_" + segmentId);
        }
    }
    private void executeSql(String sql) throws TaskException {
        Result result = runQuery(sql);
        if (result.hasErrors())
            throw new TaskException("Failed to run sql: " + result.getErrors().get(0));
        result.discard();
    }

    public void setSegmentListScript(String segmentListScript) {
        this.segmentListScript = segmentListScript;
    }
    public String getSegmentListScript() {
        return this.segmentListScript;
    }
    public void setSqlStatementScript(String sqlStatementScript) {
        this.sqlStatementScript = sqlStatementScript;
    }
    public String getSqlStatementScript() {
        return this.sqlStatementScript;
    }
    public void setViewPrefix(String viewPrefix ) {
        this.viewPrefix = viewPrefix;
    }
    public String getViewPrefix() {
        return this.viewPrefix;
    }
    public boolean getDropViews() {
        return this.dropViews;
    }
    public void setDropViews(boolean dropViews) {
        this.dropViews = dropViews;
    }

    private void info(String text) {
        getLogger().info(text);
    }


}